--======================================
--  Create T-SQL Trigger Template
--======================================
USE TIENGUITIETKIEM
GO
--trigger insert khach hang
IF OBJECT_ID ('TRG_INSERT_KHACH_HANG','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_KHACH_HANG 
GO
CREATE TRIGGER TRG_INSERT_KHACH_HANG 
   ON  KHACH_HANG 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = MA_KH from KHACH_HANG order by MA_KH desc
        UPDATE KHACH_HANG SET MA_KH = dbo.FN_NextID (@lastid,'KH',7),SOTIEN_NO=0,SOLUONG_SOTIETKIEM=0,SOTIENLAI_HIENCO=0,GIATRI_TINPHIEU_HIENCO=0,SOTIENGUI_HIENCO=0,SOLUONG_TINPHIEU=0 where MA_KH = ''
	END;
GO
select * from KHACH_HANG
--TRIGGER INSERT TAO MA TU DONG SOTIETKIEM
IF OBJECT_ID ('TRG_INSERT_SOTIETKIEM','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_SOTIETKIEM 
GO

CREATE TRIGGER TRG_INSERT_SOTIETKIEM 
   ON  SOTIETKIEM 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10),@SoTienGui float,@MaKH varchar(20),@SoTienGuiHienCo float,
		@NgayMoSo datetime,@MaSo varchar(20),@MaLoaiTietKiem varchar(30),@KyHan int,@MaLoaitien varchar(30),@TenDonViTien varchar(30),
		@TyLeTienVND_USD float,@TyLeTienUSD_VND float,@SoLuongSTK int
		select @SoTienGui=SOTIEN_GUI,@MaKH=MA_KH,@NgayMoSo=NGAYMO_SO,@MaSo=MA_SO,@MaLoaiTietKiem=MA_LOAITIETKIEM from inserted
		select @SoTienGuiHienCo=SOTIENGUI_HIENCO,@SoLuongSTK=SOLUONG_SOTIETKIEM from KHACH_HANG where MA_KH=@MaKH
		select @KyHan=KYHAN,@MaLoaitien=MA_LOAITIEN from LOAITIETKIEM where MA_LOAITIETKIEM=@MaLoaiTietKiem
		select @TenDonViTien=DON_VI_TIEN from LOAITIEN where MA_LOAITIEN=@MaLoaitien
		select @TyLeTienUSD_VND=USD_VND,@TyLeTienVND_USD=VND_USD from THAMSO
		--cap nhat du lieu so tiet kiem
		--doi tien theo don vi tien, doi tien vnd sang usd
		if @TenDonViTien='vnd'
		begin
				set @SoTienGui=@SoTienGui*@TyLeTienVND_USD
				print @SoTienGui
		end
		if @KyHan=0
		begin
			update SOTIETKIEM
			set NGAY_DEN_HAN='',TRANGTHAI=1,SOTIEN_GUI=@SoTienGui
			where MA_SO=@MaSo
		end
		if @KyHan>0
		begin
			update SOTIETKIEM
			set NGAY_DEN_HAN=@NgayMoSo+@KyHan*30,TRANGTHAI=1
			where MA_SO=@MaSo
		end
		--cap nhat du lieu bang khach hang
		update KHACH_HANG
		set SOTIENGUI_HIENCO=@SoTienGuiHienCo+@SoTienGui,SOLUONG_SOTIETKIEM=@SoLuongSTK+1
		where MA_KH=@MaKH
		--cap nhat ma so tiet kiem
        SELECT TOP 1 @LastID = MA_SO from SOTIETKIEM order by MA_SO desc
        UPDATE SOTIETKIEM SET MA_SO = dbo.FN_NextID (@lastid,'MS',7) where MA_SO = ''
	END;
GO
--TRIGGER MA TU DONG TREN BANG LOAI TIET KIEM
IF OBJECT_ID ('TRG_INSERT_LOAITIETKIEM','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_LOAITIETKIEM 
GO

CREATE TRIGGER TRG_INSERT_LOAITIETKIEM
   ON  LOAITIETKIEM 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = MA_LOAITIETKIEM from LOAITIETKIEM order by MA_LOAITIETKIEM desc
        UPDATE LOAITIETKIEM SET MA_LOAITIETKIEM = dbo.FN_NextID (@lastid,'LTK',7) where MA_LOAITIETKIEM = ''
	END;
GO
--TRIGGER INSERT MA TU DONG TREN BANG LOAITIEN
IF OBJECT_ID ('TRG_INSERT_LOAITIEN','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_LOAITIEN 
GO

CREATE TRIGGER TRG_INSERT_LOAITIEN
   ON  LOAITIEN 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = MA_LOAITIEN from LOAITIEN order by MA_LOAITIEN desc
        UPDATE LOAITIEN SET MA_LOAITIEN = dbo.FN_NextID (@lastid,'LT',7) where MA_LOAITIEN = ''
	END;
GO
--TRIGER INSERT MA TU DONG TREN BANG TIN PHIEU
IF OBJECT_ID ('TRG_INSERT_TINPHIEU','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_TINPHIEU 
GO

CREATE TRIGGER TRG_INSERT_TINPHIEU
   ON  TINPHIEU 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10),@MaKH varchar(20),@MaLoaiTinPhieu varchar(20),@MenhGia float,@SoLuongTinphieu int,@NgayMua datetime,@KyHan int,
		@GiaTriTinPhieuHienCo float,@MaTinPhieu varchar(20),@SoLuongTinPhieuHienCo int
		select @MaKH=MA_KH,@MaLoaiTinPhieu=MA_LOAI_TINPHIEU,@SoLuongTinphieu=SOLUONG,@NgayMua=NGAYMUA_TINPHIEU,@MaTinPhieu=MA_TINPHIEU from inserted
		select @MenhGia=MENHGIA,@KyHan=KYHAN from LOAI_TINPHIEU
		select @SoLuongTinPhieuHienCo=SOLUONG_TINPHIEU,@GiaTriTinPhieuHienCo=GIATRI_TINPHIEU_HIENCO from KHACH_HANG where MA_KH=@MaKH
        SELECT TOP 1 @LastID = MA_TINPHIEU from TINPHIEU order by MA_TINPHIEU desc
		--update table tin phieu
		update TINPHIEU
		set NGAYDENHAN=@NgayMua+@KyHan*30,TRANGTHAI=1
		where MA_TINPHIEU=@MaTinPhieu
        UPDATE TINPHIEU 
		SET MA_TINPHIEU = dbo.FN_NextID (@lastid,'TP',7) 
		where MA_TINPHIEU = ''
		--update table khach hang
		update KHACH_HANG
		set SOLUONG_TINPHIEU=@SoLuongTinPhieu+@SoLuongTinPhieuHienCo,GIATRI_TINPHIEU_HIENCO=@GiaTriTinPhieuHienCo+@SoLuongTinphieu*@MenhGia
		where MA_KH=@MaKH
	END;
GO
--TRIGGER SINH MA TU DONG TREN BANG LOAI TIN PHEU
IF OBJECT_ID ('TRG_INSERT_LOAITINPHIEU','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_LOAITINPHIEU 
GO

CREATE TRIGGER TRG_INSERT_LOAITINPHIEU
   ON  LOAI_TINPHIEU 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = MA_LOAI_TINPHIEU from LOAI_TINPHIEU order by MA_LOAI_TINPHIEU desc
        UPDATE LOAI_TINPHIEU SET MA_LOAI_TINPHIEU = dbo.FN_NextID (@lastid,'LTP',7) where MA_LOAI_TINPHIEU = ''
	END;
GO
--trigger sinh ma tu dong tren bang phieu gui
IF OBJECT_ID ('TRG_INSERT_PHIEU_GUI','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_PHIEU_GUI 
GO

CREATE TRIGGER TRG_INSERT_PHIEU_GUI
   ON  PHIEU_GUI 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10),@MaLoaiTien varchar(20),@SoTienGui float,@TenDonViTien varchar(30),@TyLeVND_USD float,@MaSo varchar(30),@MaKH varchar(30),@TongSoTienGuiHienCo float,
		@SoTienGuiHienCo float
		select @SoTienGui=SOTIEN_GUI,@MaLoaiTien=MA_LOAITIEN,@MaSo=MA_SO from inserted
		select @MaKH=MA_KH,@SoTienGuiHienCo=SOTIEN_GUI from SOTIETKIEM where MA_SO=@MaSo
		select @TenDonViTien=DON_VI_TIEN from LOAITIEN where MA_LOAITIEN=@MaLoaiTien
		select @TyLeVND_USD=VND_USD from THAMSO
		select @TongSoTienGuiHienCo=SOTIENGUI_HIENCO from KHACH_HANG where MA_KH=@MaKH
		if LEN(@TenDonViTien)>0
		begin
				if @TenDonViTien='vnd'
				begin
						set @SoTienGui=@SoTienGui*@TyLeVND_USD
				end
		end
		--cap nhat thong tin so tien gui vao so tiet kiem
		update SOTIETKIEM
		set SOTIEN_GUI=@SoTienGui+@SoTienGuiHienCo
		where MA_SO=@MaSo
		--cap nhat so tien sang bang khach hang
		update KHACH_HANG
		set SOTIENGUI_HIENCO=@SoTienGui+@SoTienGuiHienCo
		where MA_KH=@MaKH
        SELECT TOP 1 @LastID = MA_PHIEU_GUI from PHIEU_GUI order by MA_PHIEU_GUI desc
        UPDATE PHIEU_GUI SET MA_PHIEU_GUI = dbo.FN_NextID (@lastid,'PG',7) where MA_PHIEU_GUI = ''
	END;
GO
--TRIGGER TREN PHIEU RUT
IF OBJECT_ID ('TRG_INSERT_PHIEURUT','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_PHIEURUT
GO

CREATE TRIGGER TRG_INSERT_PHIEURUT
   ON  PHIEU_RUT 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)

        SELECT TOP 1 @LastID = MA_PHIEU_RUT from PHIEU_RUT order by MA_PHIEU_RUT desc
        UPDATE PHIEU_RUT SET MA_PHIEU_RUT = dbo.FN_NextID (@lastid,'PR',7) where MA_PHIEU_RUT = ''
	END;
GO
---trigger tren chi tiet phieu rut
IF OBJECT_ID ('TRG_INSERT_CHITIET_PHIEURUT','TR') IS NOT NULL
   DROP TRIGGER TRG_INSERT_CHITIET_PHIEURUT
GO

CREATE TRIGGER TRG_INSERT_CHITIET_PHIEURUT
   ON  CHITIET_PHIEU_RUT 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10),@MaLoaiTien varchar(30),@MaSo varchar(30),@MaLoaiTietKiem varchar(30),@MaKH varchar(30),@TenLoaiTien varchar(30)
		select @MaSo=MA_SO from inserted
		select @MaKH=MA_KH,@MaLoaiTietKiem=MA_LOAITIETKIEM from SOTIETKIEM where MA_SO=@MaSo
		select @MaLoaiTien=MA_LOAITIEN from LOAITIETKIEM where MA_LOAITIETKIEM=@MaLoaiTietKiem
		select @TenLoaiTien=DON_VI_TIEN from LOAITIEN where MA_LOAITIEN=@MaLoaiTien
        SELECT TOP 1 @LastID = MA_PHIEU_RUT from PHIEU_RUT order by MA_PHIEU_RUT desc
        UPDATE PHIEU_RUT SET MA_PHIEU_RUT = dbo.FN_NextID (@lastid,'CTPR',7) where MA_PHIEU_RUT = ''
	END;